﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Entities;
using System.Data;

namespace DataAccess
{
    public partial class MSSQLDataAccess : IDataAccess
    {
        #region IDataAccess Members

        public TableInformation QueryValidation(ConnectionInformation connectionInformation, string QueryString)
        {
            TableInformation tableInformation = new TableInformation(); ;

            tableInformation.DataTable = ExecuteQuery(connectionInformation, QueryString);

            StringBuilder sb = new StringBuilder("Select Column_name,data_type,table_Name from Information_Schema.Columns where Table_Name in ('");
            string[] TableNameList = ExtractTableName(QueryString);
            foreach (string s in TableNameList)
            {
                sb.Append(s);
                sb.Append("', '");
            }
            sb.Append(" ')");

            sb.Append(" and Column_name in ('");

            foreach (DataColumn column in tableInformation.DataTable.Columns)
            {
                sb.Append(column.ColumnName);
                sb.Append("', '");
            }

            sb.Append(" ')");

            QueryString = sb.ToString();

            DataTable dataTable = ExecuteQuery(connectionInformation, QueryString);

            List<Column> Columnlist = new List<Column>();
            int count = dataTable.Rows.Count;
            for (int i = 0; i < count; i++)
            {
                if (TableNameList.Length == 1)
                    Columnlist.Add(new Column() { Name = dataTable.Rows[i][0].ToString(), Type1 = dataTable.Rows[i][1].ToString(), IsImported = true });
                else
                    Columnlist.Add(new Column() { Name = dataTable.Rows[i][2].ToString() + "." + dataTable.Rows[i][0].ToString(), Type1 = dataTable.Rows[i][1].ToString(), IsImported = true });
            }

            //Column.TypeList = GetSystemDataTypes(connectionInformation);

            tableInformation.ColumnList = Columnlist;

            return tableInformation;
        }
        #endregion

        private string[] ExtractTableName(string QueryString)
        {
            QueryString = QueryString.ToLower();
            int fromIndex = QueryString.IndexOf("from");
            int whereIndex = QueryString.IndexOf("where");
            fromIndex += 4;

            if (whereIndex != -1)
                QueryString = QueryString.Substring(fromIndex, whereIndex - fromIndex);
            else
                QueryString = QueryString.Substring(fromIndex);

            QueryString = QueryString.Replace(" ", "");

            string[] str = QueryString.Split(","[0]);

            int l = str.Length;

            for (int i = 0; i < l; i++)
            {
                if (str[i].Contains('.'))
                {
                    str[i] = str[i].Split("."[0])[1];
                }
            }
            return str;
        }
    }
}